from flask import Flask, render_template, jsonify, request, g
from flask_cors import CORS
import sqlite3
import os
import uuid
from datetime import datetime
from werkzeug.security import generate_password_hash, check_password_hash

# 初始化Flask应用
app = Flask(__name__)
CORS(app)  # 允许跨域请求

# 数据库配置
DATABASE = 'shopping.db'

# 数据库连接函数
def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row  # 使查询结果可以通过列名访问
    return db

# 关闭数据库连接
@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

# 初始化数据库
def init_db():
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()
        
        # 添加初始商品数据
        init_products = [
            (1, '无线蓝牙耳机', 299.99, 399.99, 'https://picsum.photos/seed/headphones/400/300', '电子产品', 4.8, 124, '高品质无线蓝牙耳机，降噪功能，长达24小时续航，支持快速充电，兼容各种设备。'),
            (2, '智能手表', 599.99, 699.99, 'https://picsum.photos/seed/watch/400/300', '电子产品', 4.6, 89, '全触摸智能手表，支持心率监测、睡眠分析、运动追踪，防水设计，可连接手机接收通知。'),
            (3, '纯棉T恤', 79.99, 99.99, 'https://picsum.photos/seed/tshirt/400/300', '服装', 4.5, 215, '100%纯棉T恤，舒适透气，经典款式，多种颜色可选，适合日常穿着。'),
            (4, '机械键盘', 199.99, 249.99, 'https://picsum.photos/seed/keyboard/400/300', '电子产品', 4.9, 156, 'RGB背光机械键盘，青轴开关，全键无冲，铝合金面板，持久耐用。'),
            (5, '北欧风格台灯', 129.99, 159.99, 'https://picsum.photos/seed/lamp/400/300', '家居', 4.4, 78, '简约北欧风格台灯，可调亮度，LED节能光源，适合卧室和书房使用。'),
            (6, '健身手环', 149.99, 179.99, 'https://picsum.photos/seed/bracelet/400/300', '电子产品', 4.3, 102, '多功能健身手环，支持计步、心率监测、睡眠分析，防水设计，长达14天续航。'),
            (7, '商务笔记本', 45.99, 59.99, 'https://picsum.photos/seed/notebook/400/300', '图书', 4.7, 63, '高端商务笔记本，优质纸张，耐用封面，适合会议记录和日常笔记。'),
            (8, '无线充电器', 89.99, 109.99, 'https://picsum.photos/seed/charger/400/300', '电子产品', 4.2, 95, '15W快速无线充电器，兼容多种设备，智能识别，过充保护，方便实用。')
        ]
        
        db.cursor().executemany(
            'INSERT INTO products (id, name, price, original_price, image, category, rating, reviews, description) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
            init_products
        )
        db.commit()

# 商品相关API
@app.route('/api/products', methods=['GET'])
def get_products():
    """获取所有商品"""
    db = get_db()
    cursor = db.execute('SELECT * FROM products')
    products = [dict(row) for row in cursor.fetchall()]
    return jsonify(products)

@app.route('/api/products/<int:product_id>', methods=['GET'])
def get_product(product_id):
    """获取单个商品详情"""
    db = get_db()
    cursor = db.execute('SELECT * FROM products WHERE id = ?', (product_id,))
    product = cursor.fetchone()
    if product is None:
        return jsonify({'error': '商品不存在'}), 404
    return jsonify(dict(product))

@app.route('/api/products', methods=['POST'])
def add_product():
    """添加新商品"""
    data = request.json
    if not all(k in data for k in ('name', 'price', 'category')):
        return jsonify({'error': '缺少必要的商品信息'}), 400
    
    db = get_db()
    cursor = db.execute(
        '''INSERT INTO products (name, price, original_price, image, category, rating, reviews, description)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)''',
        (data['name'], data['price'], data.get('original_price', data['price']), 
         data.get('image', ''), data['category'], data.get('rating', 0), 
         data.get('reviews', 0), data.get('description', ''))
    )
    db.commit()
    
    product_id = cursor.lastrowid
    return jsonify({'id': product_id, 'message': '商品添加成功'}), 201

# 用户相关API
@app.route('/api/users/register', methods=['POST'])
def register_user():
    """用户注册"""
    data = request.json
    if not all(k in data for k in ('username', 'email', 'password')):
        return jsonify({'error': '缺少必要的用户信息'}), 400
    
    db = get_db()
    
    # 检查邮箱是否已注册
    cursor = db.execute('SELECT id FROM users WHERE email = ?', (data['email'],))
    if cursor.fetchone():
        return jsonify({'error': '该邮箱已被注册'}), 400
    
    # 哈希密码
    hashed_password = generate_password_hash(data['password'])
    
    # 创建用户
    cursor = db.execute(
        'INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)',
        (data['username'], data['email'], hashed_password)
    )
    db.commit()
    
    user_id = cursor.lastrowid
    return jsonify({'id': user_id, 'message': '注册成功'}), 201

@app.route('/api/users/login', methods=['POST'])
def login_user():
    """用户登录"""
    data = request.json
    if not all(k in data for k in ('email', 'password')):
        return jsonify({'error': '请提供邮箱和密码'}), 400
    
    db = get_db()
    cursor = db.execute('SELECT * FROM users WHERE email = ?', (data['email'],))
    user = cursor.fetchone()
    
    if user is None or not check_password_hash(user['password_hash'], data['password']):
        return jsonify({'error': '邮箱或密码错误'}), 401
    
    # 生成简单的令牌（实际应用中应该使用JWT等更安全的方式）
    token = str(uuid.uuid4())
    
    # 保存令牌
    db.execute(
        'UPDATE users SET token = ?, last_login = ? WHERE id = ?',
        (token, datetime.now().isoformat(), user['id'])
    )
    db.commit()
    
    return jsonify({
        'token': token,
        'user': {
            'id': user['id'],
            'username': user['username'],
            'email': user['email']
        }
    })

# 购物车相关API
@app.route('/api/cart', methods=['GET'])
def get_cart():
    """获取当前用户的购物车"""
    # 实际应用中应该验证用户token
    user_id = 1  # 这里简化处理，假设用户已登录
    
    db = get_db()
    cursor = db.execute(
        '''SELECT cart_items.*, products.name, products.price, products.image 
           FROM cart_items 
           JOIN products ON cart_items.product_id = products.id 
           WHERE cart_items.user_id = ?''',
        (user_id,)
    )
    cart_items = [dict(row) for row in cursor.fetchall()]
    
    return jsonify(cart_items)

@app.route('/api/cart', methods=['POST'])
def add_to_cart():
    """添加商品到购物车"""
    data = request.json
    if not all(k in data for k in ('product_id', 'quantity')):
        return jsonify({'error': '缺少必要的购物车信息'}), 400
    
    # 实际应用中应该验证用户token
    user_id = 1  # 这里简化处理，假设用户已登录
    product_id = data['product_id']
    quantity = int(data['quantity'])
    
    # 检查商品是否存在
    db = get_db()
    cursor = db.execute('SELECT id FROM products WHERE id = ?', (product_id,))
    if not cursor.fetchone():
        return jsonify({'error': '商品不存在'}), 404
    
    # 检查购物车中是否已有该商品
    cursor = db.execute(
        'SELECT id, quantity FROM cart_items WHERE user_id = ? AND product_id = ?',
        (user_id, product_id)
    )
    cart_item = cursor.fetchone()
    
    if cart_item:
        # 更新数量
        new_quantity = cart_item['quantity'] + quantity
        db.execute(
            'UPDATE cart_items SET quantity = ? WHERE id = ?',
            (new_quantity, cart_item['id'])
        )
    else:
        # 添加新商品
        db.execute(
            'INSERT INTO cart_items (user_id, product_id, quantity) VALUES (?, ?, ?)',
            (user_id, product_id, quantity)
        )
    
    db.commit()
    return jsonify({'message': '商品已添加到购物车'})

@app.route('/api/cart/<int:item_id>', methods=['PUT'])
def update_cart_item(item_id):
    """更新购物车中的商品数量"""
    data = request.json
    if 'quantity' not in data:
        return jsonify({'error': '请提供商品数量'}), 400
    
    # 实际应用中应该验证用户token
    user_id = 1  # 这里简化处理，假设用户已登录
    quantity = int(data['quantity'])
    
    if quantity < 1:
        return jsonify({'error': '商品数量不能小于1'}), 400
    
    db = get_db()
    # 确保购物车项属于当前用户
    cursor = db.execute(
        'SELECT id FROM cart_items WHERE id = ? AND user_id = ?',
        (item_id, user_id)
    )
    if not cursor.fetchone():
        return jsonify({'error': '购物车项不存在'}), 404
    
    db.execute(
        'UPDATE cart_items SET quantity = ? WHERE id = ?',
        (quantity, item_id)
    )
    db.commit()
    
    return jsonify({'message': '购物车已更新'})

@app.route('/api/cart/<int:item_id>', methods=['DELETE'])
def remove_cart_item(item_id):
    """从购物车中移除商品"""
    # 实际应用中应该验证用户token
    user_id = 1  # 这里简化处理，假设用户已登录
    
    db = get_db()
    # 确保购物车项属于当前用户
    cursor = db.execute(
        'SELECT id FROM cart_items WHERE id = ? AND user_id = ?',
        (item_id, user_id)
    )
    if not cursor.fetchone():
        return jsonify({'error': '购物车项不存在'}), 404
    
    db.execute('DELETE FROM cart_items WHERE id = ?', (item_id,))
    db.commit()
    
    return jsonify({'message': '商品已从购物车移除'})

# 订单相关API
@app.route('/api/orders', methods=['GET'])
def get_orders():
    """获取当前用户的订单"""
    # 实际应用中应该验证用户token
    user_id = 1  # 这里简化处理，假设用户已登录
    
    db = get_db()
    cursor = db.execute(
        'SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC',
        (user_id,)
    )
    orders = [dict(row) for row in cursor.fetchall()]
    
    return jsonify(orders)

@app.route('/api/orders', methods=['POST'])
def create_order():
    """创建新订单"""
    data = request.json
    if not all(k in data for k in ('address', 'phone')):
        return jsonify({'error': '缺少必要的订单信息'}), 400
    
    # 实际应用中应该验证用户token
    user_id = 1  # 这里简化处理，假设用户已登录
    
    db = get_db()
    db.execute('BEGIN TRANSACTION')
    
    try:
        # 获取用户购物车中的商品
        cursor = db.execute(
            '''SELECT cart_items.product_id, cart_items.quantity, products.price 
               FROM cart_items 
               JOIN products ON cart_items.product_id = products.id 
               WHERE cart_items.user_id = ?''',
            (user_id,)
        )
        cart_items = cursor.fetchall()
        
        if not cart_items:
            db.execute('ROLLBACK')
            return jsonify({'error': '购物车为空，无法创建订单'}), 400
        
        # 计算订单总金额
        total_amount = sum(item['quantity'] * item['price'] for item in cart_items)
        
        # 创建订单
        cursor = db.execute(
            '''INSERT INTO orders (user_id, total_amount, address, phone, status, created_at)
               VALUES (?, ?, ?, ?, ?, ?)''',
            (user_id, total_amount, data['address'], data['phone'], 'pending', datetime.now().isoformat())
        )
        order_id = cursor.lastrowid
        
        # 添加订单商品
        order_items = [
            (order_id, item['product_id'], item['quantity'], item['price'])
            for item in cart_items
        ]
        db.execute(
            'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)',
            order_items
        )
        
        # 清空购物车
        db.execute('DELETE FROM cart_items WHERE user_id = ?', (user_id,))
        
        db.execute('COMMIT')
        return jsonify({'order_id': order_id, 'message': '订单创建成功'}), 201
    
    except Exception as e:
        db.execute('ROLLBACK')
        return jsonify({'error': str(e)}), 500

# 首页路由（用于测试）
@app.route('/')
def index():
    return render_template('index.html')

# 启动应用
if __name__ == '__main__':
    # 如果数据库不存在，则初始化数据库
    if not os.path.exists(DATABASE):
        init_db()
    app.run(host='127.0.0.1', port=8000, debug=True)